\documentclass{report}
\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage{textcomp}
\usepackage{geometry}
\geometry{top=15mm, bottom=15mm, left=1cm, right=1cm}
\usepackage[french]{babel}
\usepackage{pslatex}
\usepackage[colorlinks=true,urlcolor=black]{hyperref}
\usepackage{graphicx}
\usepackage{fancyhdr}
\pagestyle{fancy}
\renewcommand{\footrulewidth}{0.4pt}
\fancyfoot[L]{Uubu.fr}
\fancyfoot[R]{mar. mai 4 01:59:21 CEST 2010}
\lfoot{\Large \textit{Uubu.fr}}
\hypersetup{pdfinfo={
Title={MySQL - Moteurs de table},
Author={Sylvain Girod},
Creator={Bash script from uubu.fr's format version 1},
Producer={Bash script and PDFLaTeX},
Subject={Moteur de table et types de table},
CreationDate={D:20100504015921},
ModDate={D:20100504015921},
Keywords={mysql;db;databases}
}}
\begin{document}
 
\begin{center}
{\Huge MySQL - Moteurs de table }
\end{center}
\vspace{1cm}
 
\begin{flushleft}
 
{\large Moteur de table et types de table}
 
\vspace{10mm}
 
\hspace{1mm} On peut créer une table en spécifiant la clause ENGINE ou TYPE: \\
\hspace{1mm} \textbf{CREATE TABLE t (i INT) ENGINE = INNODB ;} \\
\hspace{1mm} \textbf{CREATE TABLE t (i INT) TYPE = MEMORY ;} \\
\hspace{1mm} si on omet cette clause, le moteur par défaut est utilisé, spécifié par la variable système table\_type. Pour convertir une table d'un type à l'autre, utilisez la commande ALTER TABLE, pour indiquer le nouveau type: \\
\hspace{1mm} \textbf{ALTER TABLE t ENGINE = MYISAM ;} \\
\hspace{1mm} \textbf{ALTER TABLE t TYPE = BDB ;} \\
 
\vspace{1cm}{\huge Moteur de table MyISAM }
 
\
 
\hspace{1mm}  Chaque table \textbf{MyISAM} est stockée en trois fichiers. Les fichiers portent le nom de la table, et ont une extension qui spécifie le type de fichier. Le fichier \textbf{.frm} stocke la définition de la table. L'index est stocké dans un fichier avec l'extension \textbf{.MYI} (MYIndex), et les données sont stockées dans un fichier avec l'extension \textbf{.MYD} (MYData).
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Toutes les clés numériques sont stockées avec l'octet de poids fort en premier, pour améliorer la compression.}
\item[\hspace{1mm} -]{Support des grands fichiers}
\item[\hspace{1mm} -]{Les lignes de taille dynamique sont bien moins fragmentées lors de l'utilisation d'insertion et d'effacement.}
\item[\hspace{1mm} -]{Le nombre maximal d'index par table est de 64}
\item[\hspace{1mm} -]{La taille maximale d'une clé est de 1000 octets}
\item[\hspace{1mm} -]{Les colonnes \textbf{BLOB} et \textbf{TEXT} peuvent être indexées.}
\item[\hspace{1mm} -]{Les valeurs NULL sont autorisées dans une colonne indexée. Elles prennent 0 à 1 octets par clé.}
\item[\hspace{1mm} -]{Les fichiers d'index sont généralement plus petits en MyISAM qu'en ISAM.}
\item[\hspace{1mm} -]{améliore l'utilisation d'espace dans l'arbre des clés.}
\item[\hspace{1mm} -]{Vous pouvez placer les fichiers de données et d'index dans différents dossiers pour obtenir plus de vitesse avec les options de table \textbf{DATA DIRECTORY} et \textbf{INDEX DIRECTORY}, dans la commande \textbf{CREATE TABLE}.}
\item[\hspace{1mm} -]{chaque colonne de caractères peut avoir un jeu de caractères distinct.}
\item[\hspace{1mm} -]{Il y a un indicateur dans le fichier \textbf{MyISAM} qui indique si la table a été correctement fermée.}
\item[\hspace{1mm} -]{myisampack peut compresser des colonnes \textbf{BLOB} et \textbf{VARCHAR}.}
\item[\hspace{1mm} -]{Support du vrai type \textbf{VARCHAR}; une colonne \textbf{VARCHAR} commence avec une taille, stockée sur 2 octets.}
\item[\hspace{1mm} -]{Les tables ayant des colonnes \textbf{VARCHAR} peuvent avoir un format de lignes fixe ou dynamique.}
\item[\hspace{1mm} -]{\textbf{VARCHAR} et \textbf{CHAR} peuvent prendre jusqu'à 64 ko.}
\item[\hspace{1mm} -]{Un index de hashage peut être utilisé avec \textbf{UNIQUE}. Cela vous permettra d'avoir un index \textbf{UNIQUE} sur toute combinaison de colonnes de la table. Vous ne pourrez pas utiliser un index \textbf{UNIQUE} pour une recherche.}
\end{description}
 
\hspace{1mm}  Les options suivantes de mysqld permettent de modifier le comportement des tables MyISAM:
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} --myisam-recover=mode]{Active le mode de restauration automatique des tables MyISAM corrompues.}
\item[\hspace{1mm} --delay-key-write=ALL]{N'écrit pas les buffers de clés entre deux écritures dans une table MyISAM.}
\end{description}
 
\hspace{1mm}  \textbf{Note}: Si vous faîtes cela, vous ne devez pas utiliser les tables MyISAM avec d'autres programmes (comme depuis un autre serveur MySQL ou avec myisamchk) lorsque la table est utilisée. Sinon, vous allez obtenir une corruption d'index.
\vspace{5mm} \\
\hspace{1mm}  Utiliser \textbf{--external-locking} n'aidera pas les tables qui utilisent \textbf{--delay-key-write}.
\vspace{5mm} \\
\hspace{1mm}  Les variables systèmes suivantes affectent le comportement des tables MyISAM
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} bulk\_insert\_buffer\_size]{La taille du cache d'index lors des insertions de masse. Note : c'est une limite par thread !}
\item[\hspace{1mm} myisam\_max\_extra\_sort\_file\_size]{Utilisée pour aider MySQL à décider quand utiliser le cache de clé lent mais sûr.}
\item[\hspace{1mm} myisam\_max\_sort\_file\_size]{N'utilise pas la méthode de tri rapide pour créer un index, si un fichier temporaire dépasserait cette taille.}
\item[\hspace{1mm} myisam\_sort\_buffer\_size]{La taille du buffer lors de la restauration de table.}
\end{description}
 
\hspace{1mm}  La restauration automatique est activée si vous lancez mysqld avec l'option \textbf{--myisam-recover}. Dans ce cas, lorsque le serveur ouvre la table MyISAM, il vérifie si la table a été marquée comme crashée ou si le compteur de tables ouvertes n'est pas zéro ou si le serveur utilise \textbf{--skip-external-locking}. Si une des conditions précédente est vraie, il arrive ceci
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{La table est analysée pour rechercher des erreurs.}
\item[\hspace{1mm} -]{Si le serveur trouve une erreur, il essaie de faire une réparation rapide (avec le tri, sans recréer de données).}
\item[\hspace{1mm} -]{Si la réparation échoue à cause d'une erreur dans le fichier de données (par exemple, une erreur de clé), le serveur essaie à nouveau, en re-créant le fichier de données.}
\item[\hspace{1mm} -]{Si la réparation échoue encore, le serveur essaie encore avec une ancienne méthode réparation (écrire les lignes les unes après les autres, sans tri). Cette méthode devrait être capable de réparer tout les types d'erreurs, et elle occupe peu de place sur le disque.}
\item[\hspace{1mm} -]{Si la restauration n'est toujours pas capable de retrouver toutes les lignes, et que vous n'avez pas spécifié l'option \textbf{FORCE} dans la valeur de l'option \textbf{--myisam-recover}, la réparation automatique s'annule, avec le message d'erreur suivant: \textbf{Error : Couldn't repair table : test.g00pages}}
\item[\hspace{1mm} -]{Si vous spécifiez la valeur \textbf{FORCE}, une alerte comme celle-ci sera écrite dans les logs: \textbf{Warning : Found 344 of 354 rows when repairing ./test/g00pages}}
\end{description}
 
\hspace{1mm}  Notez que si la valeur de restauration automatique inclut \textbf{BACKUP}, le processus de restauration créera des fichiers avec des noms de la forme \textbf{tbl\_name-datetime.BAK}. Vous devriez avoir une tâche régulière avec cron pour supprimer automatiquement ces fichiers dans les bases de données pour nettoyer le volume.
\vspace{5mm} \\
 
 
\hspace{1mm}  Pour avoir une approximation de la taille du fichier d'index, faire la somme de \textbf{(longueur\_clef+4)/0.67} pour toutes les clefs. (c'est le pire des cas où les clefs sont insérées dans l'ordre et qu'aucune n'est compressée).
\vspace{5mm} \\
 
 
\hspace{1mm}  \textbf{MyISAM} supporte 3 différents types de tables. Deux des trois sont choisis automatiquement selon le type de colonne que vous utilisez. Le troisième, tables compressées, ne peut être crée qu'avec l'outil \textbf{myisampack}. Quand vous créez une table avec \textbf{CREATE} ou en modifiez la structure avec \textbf{ALTER} vous pouvez, pour les tables n'ayant pas de champ \textbf{BLOB} forcer le type de table en \textbf{DYNAMIC} ou \textbf{FIXED} avec l'option \textbf{ROW\_FORMAT=\#} des tables. Bientôt, vous pourrez compresser/décompresser les tables en spécifiant \textbf{ROW\_FORMAT=compressed | default} à \textbf{ALTER TABLE}.
\vspace{5mm} \\
 
 
\hspace{1mm}  \textbf{Tables statiques}: Ceci est le format par défaut. Il est utilisé lorsque la table ne contient pas de colonnes de type \textbf{VARCHAR}, \textbf{BLOB}, ou \textbf{TEXT}. Ce format est le plus simple et le plus sûr. C'est aussi le format sur disque le plus rapide.
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Toutes les colonnes \textbf{CHAR}, \textbf{NUMERIC}, et \textbf{DECIMAL} sont complétées par des espaces jusqu'à atteindre la longueur totale de la colonne.}
\item[\hspace{1mm} -]{Très rapide.}
\item[\hspace{1mm} -]{Facile à mettre en cache.}
\item[\hspace{1mm} -]{Facile à reconstruire après un crash, car les enregistrements sont localisés dans des positions fixées.}
\item[\hspace{1mm} -]{N'a pas à être réorganisé (avec \textbf{myisamchk}) sauf si un grand nombre de lignes est effacé et que vous voulez retourner l'espace libéré au système d'exploitation.}
\item[\hspace{1mm} -]{Requière généralement plus d'espace disque que les tables dynamiques.}
\end{description}
 
\hspace{1mm}  \textbf{Tables à format de ligne dynamiques}: Ce format est utilisé avec les tables qui contiennent des colonnes de type \textbf{VARCHAR}, \textbf{BLOB} ou \textbf{TEXT}, ou si la table a été créée avec l'option \textbf{ROW\_FORMAT=dynamic}. Vous pouvez utiliser la commande \textbf{SQL OPTIMIZE table} ou Shell \textbf{myisamchk} pour défragmenter une table.
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Toutes les colonnes de type chaîne sont dynamiques (hormis celle qui sont de taille inférieure à 4).}
\item[\hspace{1mm} -]{Chaque ligne est précédée d'un octet qui indique quelles sont les lignes vides ('', bit à 1) et celle qui le ne sont pas (bit à 0). Une colonne vide n'est pas la même choses qu'une colonne qui contient \textbf{NULL}. Si une colonne a une taille de zéro après avoir supprimé les espaces finaux, ou un nombre a une valeur de zéro, il est marqué dans cet octet, et la colonne sera ignorée sur le disque. Les chaînes non vides sont sauvées avec un octet de plus pour y stocker la taille.}
\item[\hspace{1mm} -]{Ce format prend généralement moins de place que des tables à format fixe.}
\item[\hspace{1mm} -]{Chaque ligne consomme autant d'espace que nécessaire. Si une ligne devient trop grande, elle sera coupée en blocs et écrites dans le fichier de données. Cela engendre la fragmentation du fichier de données. Dans ce cas, vous pouvez avoir à exécuter la commande \textbf{myisamchk -r} de temps en temps pour améliorer les performances. Utilisez \textbf{myisamchk -ei tbl\_name} pour obtenir des statistiques.}
\item[\hspace{1mm} -]{Ce format de table n'est pas toujours facile à reconstituer après un crash, car une ligne peut être fragmentée en de nombreux blocs, et un fragment peut manquer.}
\end{description}
 
\hspace{1mm} La taille d'une ligne de format variable se calcule avec
 
{\ttfamily
3\\ + (nombre de colonnes + 7) / 8\\ + (nombre de colonnes de tailles chars)\\ + taille compactée des colonnes numériques\\ + taille des chaînes\\ + (nombre de colonne de valeur NULL + 7) / 8\\
}
\vspace{8mm}
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Il y a un aussi un supplément de 6 octets pour chaque lien. Une ligne de format dynamique utilise un lien à chaque fois qu'une modification cause un agrandissement de la ligne. Chaque nouveau bloc lié fait au moins 20 octets, pour que le prochain agrandissement utilise aussi ce bloc. Si ce n'est pas le cas, un nouveau bloc sera lié, avec un autre coût de 6 octets. Vous pouvez vérifier le nombre de liens dans une table avec la commande \textbf{myisamchk -ed}. Tous les liens sont supprimés avec la commande \textbf{myisamchk -r}.}
\end{description}
 
\hspace{1mm}  \textbf{tables compressées}: C'est un type en lecture seule qui est généré avec l'outil optionnel \textbf{myisampack}.
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Les tables compressées prennent très peu d'espace disque.}
\item[\hspace{1mm} -]{Chaque ligne est compressée séparément (optimisation des accès). L'en-tête d'un enregistrement est fixé (1-3 octets) selon le plus grand enregistrement dans la table. Chaque colonne est compressée différemment. Quelques un des types de compressions sont:}
\end{description}
 
\begin{description} \normalsize
\item[\hspace{6mm} -]{Compression des espaces en suffixe.}
\item[\hspace{6mm} -]{Compression des espaces en préfixe.}
\item[\hspace{6mm} -]{Les nombres avec la valeur 0 sont stockés en utilisant 1 octet.}
\item[\hspace{6mm} -]{Si les valeurs dans une colonne de type entier ont un petit intervalle, la colonne est stockée en utilisant le type le plus petit possible.}
\item[\hspace{6mm} -]{Si une colonne n'a qu'un petit éventail de valeurs, son type est changé en ENUM.}
\item[\hspace{6mm} -]{Une colonne peut utiliser une combinaison des compressions précédentes.}
\item[\hspace{6mm} -]{Peut gérer les enregistrements de tailles fixes ou variables.}
\end{description}
 
\hspace{1mm}  Vous pouvez réparer une table corrompue avec \textbf{REPAIR TABLE}. Vous pouvez aussi réparer une table, lorsque \textbf{mysqld} ne fonctionne pas, avec la commande \textbf{myisamchk}
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Tables assemblées MERGE }
 
\
 
\hspace{1mm}  Une table \textbf{MERGE} est un groupe de tables \textbf{MyISAM} identiques qui sont utilisées comme une seule. "Identique" signifie que toutes les tables ont la même structure de colonnes et d'index
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Tables MEMORY (HEAP) }
 
\
 
\hspace{1mm}  Le moteur de stockage \textbf{MEMORY} crée des tables dont le contenu est stocké en mémoire.
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Vous pouvez avoir des clés non-uniques dans une table \textbf{MEMORY}. (C'est une fonctionnalité rare pour les index hash).}
\item[\hspace{1mm} -]{Si vous avez un index hash sur une table \textbf{HEAP} avec un haut degré de duplication (de nombreux valeurs d'index contiennent la même valeur), les modifications dans cette table peuvent affecter les valeurs des clés et toutes les suppressions seront plus lentes. Le facteur de ralentissement est proportionnel au degré de duplication (ou inversement proportionnel à la cardinalité).}
\item[\hspace{1mm} -]{Les tables \textbf{HEAP} utilisent un format de ligne fixe.}
\item[\hspace{1mm} -]{\textbf{HEAP} ne supporte pas les colonnes de type \textbf{BLOB/TEXT}.}
\item[\hspace{1mm} -]{\textbf{HEAP} ne supporte pas les colonnes de type \textbf{AUTO\_INCREMENT}.}
\item[\hspace{1mm} -]{Les tables \textbf{HEAP} sont partagées entre tous les clients (comme une autre table).}
\item[\hspace{1mm} -]{La caractéristique des tables \textbf{MEMORY} qui fait que les tables sont stockées en mémoire est partagée avec les tables internes que le serveur crée à la volée lors du traitement des requêtes. Cependant, les tables internes ont aussi la capacité d'être converties en tables disques automatiquement, si elles deviennent trop grandes. La taille limite est déterminée par la valeur de \textbf{tmp\_table\_size}.}
\end{description}
 
\hspace{1mm}  Les tables \textbf{MEMORY} ne peuvent pas être converties en tables disques. Pour vous assurer que vous ne faîtes rien de dangereux pour le serveur, vous pouvez utiliser la variable système \textbf{max\_heap\_table\_size} pour imposer une taille maximale aux tables \textbf{MEMORY}. Pour des tables individuelles, vous pouvez utiliser l'option de table \textbf{MAX\_ROWS} avec la commande \textbf{CREATE TABLE}.
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} -]{Vous avez besoin de suffisamment de mémoire pour accepter toutes les tables \textbf{HEAP} que vous allez utiliser simultanément.}
\item[\hspace{1mm} -]{Pour libérer de la mémoire, vous devez exécuter la commande \textbf{DELETE FROM heap\_table}, \textbf{TRUNCATE heap\_table} ou \textbf{DROP TABLE heap\_table}.}
\item[\hspace{1mm} -]{Si vous voulez remplir les tables \textbf{MEMORY} au lancement du serveur MySQL, vous pouvez utiliser l'option \textbf{--init-file}. Par exemple, vous pouvez mettre les commandes telles que \textbf{INSERT INTO ... SELECT} et \textbf{LOAD DATA INFILE} pour lire des données dans une source de données persistante.}
\item[\hspace{1mm} -]{Si vous utilisez la réplication, les tables \textbf{MEMORY} du maître se vident à l'extinction. Cependant, un esclave peut ne pas s'apercevoir que ces tables ont été vidées, et il risque de retourner des données invalides si vous l'utilisez. lorsqu'une table \textbf{MEMORY} est utilisée sur le maître, il émet une commande \textbf{DELETE FROM} automatiquement, pour synchroniser l'esclave et le maître. Notez que même avec cette stratégie, l'esclave aura des données obsolètes entre le moment où le maître s'éteint et celui où il est redémarré. Mais si vous utilisez l'option \textbf{--init-file} pour remplir la table \textbf{MEMORY} au lancement du serveur, elle s'assurera que cette intervalle est bien null.}
\end{description}
 
\hspace{1mm} La mémoire nécessaire pour les tables HEAP sont \\
\hspace{1mm} \textbf{SUM\_OVER\_ALL\_KEYS(max\_length\_of\_key + sizeof(char*) * 2)} \\
\hspace{1mm} \textbf{+ ALIGN(length\_of\_row+1, sizeof(char*))} \\
 
\begin{description} \normalsize
\item[\hspace{1mm} ALIGN()]{représente un facteur d'arrondi, car la taille de la ligne doit faire exactement un multiple de la taille du pointeur de char. \textbf{sizeof(char*)} vaut 4 sur les machines 32 bits et 8 sur une machine 64 bits.}
\end{description}
 
\vspace{1cm}{\huge Tables BDB ou BerkeleyDB }
 
\
 
\hspace{1mm}  En utilisant les tables \textbf{BDB}, vos tables ont plus de chances de survivre aux crashs, et vous avez accès à \textbf{COMMIT} et \textbf{ROLLBACK} avec les transactions. Les options suivantes de mysqld peuvent être utilisées pour modifier le comportement des tables BDB:
\vspace{5mm} \\
 
 
\begin{description} \normalsize
\item[\hspace{1mm} --bdb-home=répertoire]{Répertoire de base des tables BDB. Cela doit être le même répertoire que vous avez utilisés pour --datadir.}
\item[\hspace{1mm} --bdb-lock-detect=\#]{Détection des verrouillages Berkeley. (DEFAULT, OLDEST, RANDOM, ou YOUNGEST).}
\item[\hspace{1mm} --bdb-logdir=répertoire]{Répertoire des fichiers de log de Berkeley DB.}
\item[\hspace{1mm} --bdb-no-recover]{Ne pas démarrer Berkeley DB en mode de restauration.}
\item[\hspace{1mm} --bdb-no-sync]{Ne pas vider les tampons synchroniquement.}
\item[\hspace{1mm} --bdb-shared-data]{Démarrer Berkeley DB en mode multi-processus (Ne pas utiliser DB\_PRIVATE lors de l'initialisation de Berkeley DB)}
\item[\hspace{1mm} --bdb-tmpdir=répertoire]{Répertoire des fichiers temporaires de Berkeley DB.}
\item[\hspace{1mm} --skip-bdb]{Désactive l'utilisation des tables BDB.}
\end{description}
 
\hspace{1mm}  Les variables systèmes suivante affectent le comportement des tables BDB. \textbf{bdb\_max\_lock} est le nombre maximal de verrous actifs sur une table BDB. Si vous utilisez \textbf{--skip-bdb}, MySQL n'initialisera pas la bibliothèque Berkeley DB et cela économisera beaucoup de mémoire. Bien sûr, vous ne pouvez pas utiliser les table BDB si vous utilisez cette option. Si vous essayez de créer une table BDB, MySQL créera une table MyISAM à la place.
\vspace{5mm} \\
 
 
\hspace{1mm}  Normalement, vous devez démarrer mysqld sans \textbf{--bdb-no-recover} si vous avez l'intention d'utiliser des tables BDB. Cela peut cependant vous poser des problèmes si vous essayez de démarrer mysqld alors que des fichiers de log BDB sont corrompus. Vous pouvez spécifier le nombre maximal de verrous avec \textbf{bdb\_max\_lock} (10000 par défaut) que vous pouvez activer sur une table BDB. Vous devez l'augmenter si vous obtenez des erreurs du type: \textbf{bdb : Lock table is out of available locks Got error 12 from ...} lorsque vous avez fait de longues transactions ou quand mysqld doit examiner beaucoup de lignes pour calculer la requête. Vous pouvez aussi changer les options \textbf{binlog\_cache\_size} et \textbf{max\_binlog\_cache\_size} si vous utilisez de grandes transactions multi-lignes.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Moteur de table EXAMPLE }
 
\
 
\hspace{1mm}  C'est un moteur "bidon" qui ne fait rien du tout. Son but est de fournir des exemples au niveau du code source de MySQL pour illustrer l'écriture d'un moteur de table. En tant que tel, il intéressera surtout les développeurs.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Moteur de table FEDERATED }
 
\
 
\hspace{1mm}  C'est un moteur de table qui accède à des tables dans une base de données distante, plutôt que dans des fichiers locaux.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Moteur de table ARCHIVE }
 
\
 
\hspace{1mm}  Il est utilisé pour stocker de grande quantité de données, sans index, et de manière très économique. Le moteur \textbf{ARCHIVE} ne supporte que les commandes \textbf{INSERT} et \textbf{SELECT}: aucun effacement, remplacement ou modification. Une commande \textbf{SELECT} effectue un scan de table complet. Les enregistrements sont compressés au moment de leur insertion. Vous pouvez utiliser la commande \textbf{OPTIMIZE TABLE} pour analyser la table, et compresser encore plus.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Moteur CSV }
 
\
 
\hspace{1mm}  Ce moteur stocke les données dans un fichier texte, avec le format valeurs séparées par des virgules.
\vspace{5mm} \\
 
 
\vspace{1cm}{\huge Moteur de table InnoDB }
 
\
 
\hspace{1mm}  \textbf{InnoDB} fournit à MySQL un gestionnaire de table transactionnelle (compatible ACID), avec validation (commits), annulations (rollback) et capacités de restauration après crash. \textbf{InnoDB} utilise un verrouillage de lignes, et fournit des lectures cohérentes comme Oracle, sans verrous. Ces fonctionnalités accroissent les possibilités d'utilisation simultanées des tables, et les performances. Il n'y a pas de problème de queue de verrous avec InnoDB, car les verrous de lignes utilisent très peu de place. Les tables InnoDB sont les premières tables MySQL qui supportent les contraintes de clés étrangères (\textbf{FOREIGN KEY}).
\vspace{5mm} \\
 
 
\hspace{1mm}  Si vous ne souhaitez pas utiliser les tables \textbf{InnoDB}, vous pouvez ajouter l'option \textbf{skip-innodb} dans votre fichier d'options MySQL. Les deux ressources disques importantes gérées par InnoDB sont sa table de données et son fichier de log. Si vous ne spécifiez aucune options de configuration InnoDB, MySQL créera un fichier de données auto-croissant appelé \textbf{ibdata1} et deux fichiers de log de 5 Mo appelés \textbf{ib\_logfile0} et \textbf{ib\_logfile1} dans le dossier de données MySQL.
\vspace{5mm} \\
 
 
\hspace{1mm} Pour configurer le fichier de données InnoDB, utilisez l'option innodb\_data\_file\_path dans my.cnf: \\
\hspace{1mm} \textbf{innodb\_data\_file\_path=datafile\_spec1[ ;datafile\_spec2]...} \\
 
\vspace{1cm}{\huge Exemples }
 
\
 
\hspace{1mm} Cette configuration crée un fichier de données de 10 Mo ibdata1, auto-croissant. Il n'y a pas de dossier de sauvegarde d'indiqué: par défaut, c'est le dossier de données de MySQL. \\
\hspace{1mm} \textbf{[mysqld]} \\
\hspace{1mm} \textbf{innodb\_data\_file\_path=ibdata1:10M:autoextend} \\
\hspace{1mm} Une table contenant 50 Mo de données, appelée ibdata1 et un fichier 50 Mo auto-croissant, appelé ibdata2 dans le dossier de données \\
\hspace{1mm} \textbf{[mysqld]} \\
\hspace{1mm} \textbf{innodb\_data\_file\_path=ibdata1:50M ;ibdata2:50M:autoextend} \\
\hspace{1mm} La syntaxe complète de la spécification de fichier de données inclut le nom du fichier, sa taille, et différents attributs: \\
\hspace{1mm} \textbf{file\_name:file\_size[:autoextend[:max:max\_file\_size]]} \\
\hspace{1mm} \textbf{[mysqld]} \\
\hspace{1mm} \textbf{innodb\_data\_file\_path=ibdata1:10M:autoextend:max:500M} \\
\hspace{1mm} La ligne de configuration suivante permet au fichier ibdata1 de croître jusqu'à 500 Mo \\
\hspace{1mm} \textbf{[mysqld]} \\
\hspace{1mm} \textbf{innodb\_data\_home\_dir = /ibdata} \\
\hspace{1mm} \textbf{innodb\_data\_file\_path=ibdata1:50M ;ibdata2:50M:autoextend} \\
\hspace{1mm} crée deux fichiers appelés ibdata1 et ibdata2 mais les place dans le dossier /ibdata, \\
\hspace{1mm} \textbf{!!!!Note : InnoDB ne crée pas les dossiers} \\
\hspace{1mm} Si vous spécifier l'option innodb\_data\_home\_dir sous forme de chaîne vide, vous pouvez spécifier des noms de chemins absolus dans la valeur de innodb\_data\_file\_path. L'exemple ci-dessous est équivalent au précédent: \\
\hspace{1mm} \textbf{[mysqld]} \\
\hspace{1mm} \textbf{innodb\_data\_home\_dir =} \\
\hspace{1mm} \textbf{innodb\_data\_file\_path=/ibdata/ibdata1:50M ;/ibdata/ibdata2:50M:autoextend} \\
 
\hspace{1mm} Exemple de configuration
 
{\ttfamily
\texttt{[}mysqld\texttt{]}\\ \# Vous pouvez placer d'autres options MYSQL ici\\ \# ...\\ \# Le fichier de données doit contenir vos données et index.\\ \# Assurez vous que vous avez l'espace disque nécessaire.\\ innodb\_data\_file\_path = ibdata1:10M:autoextend\\ \#\\ \# Utilisez un buffer de taille 50 à 80 \% de votre mémoire serveur\\ set-variable = innodb\_buffer\_pool\_size=70M\\ set-variable = innodb\_additional\_mem\_pool\_size=10M\\ \#\\ \# Utilisez un fichier de log de taille 25 \% du buffer mémoire\\ set-variable = innodb\_log\_file\_size=20M\\ set-variable = innodb\_log\_buffer\_size=8M\\ \#\\ innodb\_flush\_log\_at\_trx\_commit=1\\
}
\vspace{8mm}
 
\vspace{1cm}{\huge Options de démarrage InnoDB }
 
\
 
\begin{description} \normalsize
\item[\hspace{1mm} innodb\_additional\_mem\_pool\_size]{La taille du buffer mémoire d'InnoDB, pour ses dictionnaires d'informations, et ses structures internes de données. Une valeur pratique est 2Mo, mais plus vous aurez de tables dans votre application, plus vous devrez augmenter cette valeur. Si InnoDB est à court de mémoire, il va allouer de la mémoire auprès du système, et écrire des messages dans le fichier de logs.}
\item[\hspace{1mm} innodb\_buffer\_pool\_size]{La taille de buffer mémoire que InnoDB utiliser pour mettre en cache les données et les index de tables. Plus cette valeur est grand, et moins vous ferez d'accès disques. Sur un serveur dédiés, vous pouvez monter cette valeur jusqu'à 80\% de la mémoire physique de la machine. Ne lui donnez pas une valeur trop grande, car cela peut engendrer l'utilisation de mémoire sur le disque par votre serveur.}
\item[\hspace{1mm} innodb\_data\_file\_path]{Chemin individuel vers les fichiers de données, et leur taille. Le chemin complet de chaque fichier de données est créé en concaténant \textbf{innodb\_data\_home\_dir} avec les chemins spécifiés ici. La taille du fichier est spécifiée en méga-octets, ce qui explique la présence du '\textbf{M}' après les spécifications ci-dessus. Vous pouvez donner au fichier une taille supérieure à 4 Go sur les systèmes d'exploitation qui acceptent les gros fichiers.}
\item[\hspace{1mm} ]{Sur certains systèmes, la taille doit être inférieure à 2 Go.Si vous ne spécifiez pas \textbf{innodb\_data\_file\_path}, le comportement par défaut est de créer un fichier auto-croissant de 10 Mo, appelé \textbf{ibdata1}. vous pouvez donner une taille de fichier de plus de 4Go sur les systèmes d'exploitation qui supportent les grands fichiers. Vous pouvez aussi utiliser les partition raw.}
\item[\hspace{1mm} innodb\_data\_home\_dir]{La partie commune du chemin de tous les fichiers de données InnoDB. Si vous ne mentionnez pas cette option, la valeur par défaut sera celle du dossier de données MySQL. Vous pouvez aussi spécifier une chaîne vide, et dans ce cas, les chemins spécifiés dans \textbf{innodb\_data\_file\_path} seront des chemins absolus.}
\item[\hspace{1mm} innodb\_fast\_shutdown]{Par défaut, InnoDB fait une purge complète et vide le buffer d'insertion avant une extinction. Ces opérations peuvent prendre beaucoup de temps. Si vous donnez à ce paramètre la valeur de 1, InnoDB ignore ces opérations d'extinction. Sa valeur par défaut est 1.}
\item[\hspace{1mm} innodb\_file\_io\_threads]{Nombre de pointeurs de fichier de InnoDB. Normalement, cette valeur doit être de 4.}
\item[\hspace{1mm} innodb\_file\_per\_table]{Cette option fait que InnoDB va stocker chaque table dans un fichier .ibd indépendant. Voyez la section sur les espaces de tables multiples.}
\item[\hspace{1mm} innodb\_flush\_log\_at\_trx\_commit]{Normalement, cette option vaut 1, ce qui signifie que lors de la validation de la transaction, les logs sont écrits sur le disque, et les modifications faites par la transaction deviennent permanentes, et survivront un crash de base. Si vous souhaitez réduire la sécurité de vos données, et que vous exécutez de petites transactions, vous pouvez donner une valeur de 0 à cette option, pour réduire les accès disques.}
\item[\hspace{1mm} innodb\_flush\_method]{La valeur par défaut pour cette option est fdatasync. Une autre option est O\_DSYNC.}
\item[\hspace{1mm} innodb\_force\_recovery]{Attention : cette option ne doit être définie que dans les cas où vous voulez exporter les données d'une base corrompue, dans une situation d'urgence. Les valeurs possibles de cette option vont de 1 à 6. Par mesure de sécurité, InnoDB empêche les modifications de données si la valeur de cette option est supérieure à 0.}
\item[\hspace{1mm} innodb\_lock\_wait\_timeout]{Le délai d'expiration des transactions InnoDB, en cas de blocage de verrou, avant d'annuler. InnoDB détecte automatiquement les blocages de verrous et annule alors les transactions. Si vous utilisez la commande LOCK TABLES, ou un autre gestionnaire de table transactionnelles que InnoDB dans la même transaction, un blocage de verrou peut survenir, et InnoDB ne pourra pas le détecter. Ce délai est donc pratique pour résoudre ces situations.}
\item[\hspace{1mm} innodb\_log\_arch\_dir]{Le dossier où les logs complétés doivent être archivés, si nous utilisons l'archivage de logs. La valeur de ce paramètre doit être actuellement la même que la valeur de \textbf{innodb\_log\_group\_home\_dir}.}
\item[\hspace{1mm} innodb\_log\_archive]{Cette valeur doit être actuellement de 0. Au moment de la restauration de données à partir d'une sauvegarde, à l'aide des log binaires de MySQL, il n'y a actuellement pas besoin d'archiver les fichiers de log InnoDB.}
\item[\hspace{1mm} innodb\_log\_buffer\_size]{La taille du buffer que InnoDB utilise pour écrire les log dans les fichiers de logs, sur le disque. Les valeurs utiles vont de 1 Mo à 8 Mo. Un grand buffer de log permet aux grandes transactions de s'exécuter sans avoir à écrire de données dans le fichier de log jusqu'à la validation. Par conséquent, si vous avez de grandes transactions, augmenter cette taille va réduire les accès disques.}
\item[\hspace{1mm} innodb\_log\_file\_size]{Taille de chaque fichier de log dans un groupe de log, exprimé en méga-octets. Les valeurs pratiques vont de 1Mo à une fraction de la taille du buffer de log (1 / le nombre de logs, en fait). Plus la taille est grande, moins de points de contrôles seront utilisés, réduisant les accès disques. La taille combinée des logs doit être inférieure à 4 Go sur les systèmes 32 bits.}
\item[\hspace{1mm} innodb\_log\_files\_in\_group]{Nombre de fichiers de logs dans le groupe de log. InnoDB écrit dans ces fichiers de manière circulaire. Une valeur de 2 est recommandée. C'est la valeur par défaut.}
\item[\hspace{1mm} innodb\_log\_group\_home\_dir]{Le dossier pour les fichiers de logs. Il doit avoir la même valeur que \textbf{innodb\_log\_arch\_dir}. Si vous ne spécifiez pas de paramètre de log InnoDB, la configuration par défaut va créer deux fichiers de logs de 5 Mo, appelés ib\_logfile0 et ib\_logfile1 dans le dossier de données MySQL.}
\item[\hspace{1mm} innodb\_max\_dirty\_pages\_pct]{Cette entier va de 0 à 100. Par défaut, il vaut 90. Le thread principal de InnoDB essaie de transmettre les pages au pool de buffer, pour qu'un pourcentage maximal de \textbf{innodb\_max\_dirty\_pages\_pct} soit encore en attente de flush. Si vous avez le droit de SUPER, ce pourcentage peut être changée durant l'exécution du serveur:\textbf{SET GLOBAL innodb\_max\_dirty\_pages\_pct = value ;}}
\item[\hspace{1mm} innodb\_mirrored\_log\_groups]{Nombre de copies identiques de groupe de log que nous conservons. Actuellement, cette valeur doit être au minimum de 1.}
\item[\hspace{1mm} innodb\_open\_files]{Ce n'est utile que si vous utilisez les espaces de tables multiples. Cette option spécifie que le nombre maximal de fichier .ibd que InnoDB peut garder ouvert simultanément. La valeur minimum est de 10. La valeur maximum est de 300.}
\item[\hspace{1mm} Les pointeurs de fichiers utilisés par .ibd sont réservés pour InnoDB. Ils sont indépendants de ceux spécifiés par —open-files-limit, et n'affectent pas les opérations de cache.]{innodb\_thread\_concurrency}
\item[\hspace{1mm} InnoDB essaie de garder le nombre de thread système concurents inférieur à la limite de ce paramètre. La valeur par défaut est 8. Si vous avez des problèmes de performances, et que SHOW INNODB STATUS revèle que des threads attendent des sémaphores, essayez de diminuer ou augmenter ce paramètre. Si vous avez un serveur avec de nombreux processeurs et disques,]{}
\item[\hspace{1mm} vous pouvez essayer d'augmenter la valeur, pour utiliser au mieux les ressources disponibles. Une valeur recommandée est la somme du nombre de processeurs et de disques que vous avez. Une valeur de 500 ou supérieur, supprime la vérification de concurrence.]{}
\end{description}
 
\vspace{1cm}{\huge Créer des bases InnoDB }
 
\
 
\hspace{1mm} utiliser TYPE = InnoDB. ex: \\
\hspace{1mm} \textbf{CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB ;} \\
\hspace{1mm} La quantité d'espace disponible apparaît dans la section de commentaire de la commande SHOW. Par exemple: \\
\hspace{1mm} \textbf{SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'} \\
\hspace{1mm} vous pouvez désactiver l'auto-validation avec la commande SET AUTOCOMMIT = 0 et utiliser les commandes COMMIT et ROLLBACK pour valider ou annuler vos transactions. Si vous voulez laisser le mode d'auto-validation tranquille, vous pouvez placer vos commandes entre START TRANSACTION et COMMIT ou ROLLBACK. \\
 
\vspace{1cm}{\huge Convertir des tables MyIsam vers InnoDB }
 
\
 
\hspace{1mm}  pour utiliser le moteur InnoDB pour la création de table par défaut, utiliser \textbf{default-table-type=innodb} dans le groupe \textbf{[mysqld]}. Le moyen le plus rapide pour mettre la table au format InnoDB est d'insérer directement les lignes dans une table InnoDB, c'est à dire, utiliser \textbf{ALTER TABLE ... TYPE=INNODB}, ou créer un table InnoDB vide, avec la même définition et de faire l'insertion de toutes les lignes avec \textbf{INSERT INTO ... SELECT} \textbf{*} \textbf{FROM ....}
\vspace{5mm} \\
 
 
\hspace{1mm} \textbf{INSERT INTO newtable SELECT * FROM oldtable} \\
\hspace{1mm} \textbf{WHERE yourkey > something AND yourkey <= somethingelse ;} \\
\hspace{1mm} Une fois que toutes les données ont été insérées dans la table, vous pouvez la renommer. \\
 
\hspace{1mm}  \textbf{Colonnes AUTO\_INCREMENT avec InnoDB:} Innodb va ajouter dans le dictionnaire de données un compteur spécial appelé compteur auto-increment, qui est utilisé pour assigner les nouvelles valeurs de la colonne.
\vspace{5mm} \\
 
 
\hspace{1mm}  \textbf{Contraintes de clés étrangères FOREIGN KEY}: La syntaxe des définitions de contraintes de clés étrangères de InnoDB est la suivante:
\vspace{5mm} \\
 
 
\hspace{1mm} \textbf{[CONSTRAINT symbol] FOREIGN KEY [id] (index\_col\_name, ...)} \\
\hspace{1mm} \textbf{REFERENCES tbl\_name (index\_col\_name, ...)} \\
\hspace{1mm} \textbf{[ON DELETE CASCADE | SET NULL | NO ACTION | RESTRICT]} \\
\hspace{1mm} \textbf{[ON UPDATE CASCADE | SET NULL | NO ACTION | RESTRICT]} \\
 
\hspace{1mm}  Les deux tables doivent être de type InnoDB, dans la table, il doit y avoir un \textbf{INDEX} où les clés étrangères sont listées comme première colonne, dans le même ordre, et dans la table référencée, il doit y avoir un \textbf{INDEX} où les colonnes référencées sont listées comme premières colonnes, dans le même ordre. Les préfixes d'index ne sont pas supportés pour les clés de contrainte.
\vspace{5mm} \\
 
 
\hspace{1mm} exemple:
 
{\ttfamily
CREATE TABLE parent(id INT NOT NULL,\\ PRIMARY KEY (id)\\ ) TYPE=INNODB ;\\ CREATE TABLE child(id INT, parent\_id INT,\\ INDEX par\_ind (parent\_id),\\ FOREIGN KEY (parent\_id) REFERENCES parent(id)\\ ON DELETE CASCADE\\ ) TYPE=INNODB ;\\ exemple plus complexe :\\ CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,\\ price DECIMAL,\\ PRIMARY KEY(category, id)) TYPE=INNODB ;\\ CREATE TABLE customer (id INT NOT NULL,\\ PRIMARY KEY (id)) TYPE=INNODB ;\\ CREATE TABLE product\_order (no INT NOT NULL AUTO\_INCREMENT,\\ product\_category INT NOT NULL,\\ product\_id INT NOT NULL,\\ customer\_id INT NOT NULL,\\ PRIMARY KEY(no),\\ INDEX (product\_category, product\_id),\\ FOREIGN KEY (product\_category, product\_id)\\ REFERENCES product(category, id)\\ ON UPDATE CASCADE ON DELETE RESTRICT,\\ INDEX (customer\_id),\\ FOREIGN KEY (customer\_id)\\ REFERENCES customer(id)) TYPE=INNODB ;\\
}
\vspace{8mm}
 
\vspace{1cm}{\huge InnoDB et la réplication }
 
\
 
\hspace{1mm}  Il est aussi possible d'utiliser la réplication pour que les tables de l'esclave ne soient pas les mêmes que les tables du maître. Par exemple, vous pouvez répliquer les modifications d'une table \textbf{InnoDB} sur le maître dans une table \textbf{MyISAM} sur l'esclave. Pour configurer un nouvel esclave sur le maître, vous devez faire une copie de l'espace de table InnoDB, des fichiers de log, ainsi que les fichiers .frm des tables InnoDB, et les placer sur l'esclave. Il y a des limitations mineures à la réplication \textbf{InnoDB}: \textbf{LOAD TABLE FROM MASTER} ne fonctionne pas pour les tables InnoDB. Il y a des palliatifs: 1) exportez la table du maître, et envoyez la sur l'esclave, ou, 2) utilisez \textbf{ALTER TABLE tbl\_name TYPE=MyISAM} sur le maître avant de configurer la réplication avec \textbf{LOAD TABLE tbl\_name FROM MASTER}, et ensuite, \textbf{ALTER TABLE} pour remettre les tables en mode InnoDB après cela.
\vspace{5mm} \\
 
\end{flushleft}
\end{document}
